﻿using System;
using System.Collections.Generic;
using System.Text;
using Cashier_DAO.db;
using Cashier_Model;
using System.Collections;
using System.Data;
using System.Data.Common;

namespace Cashier_DAO
{
    public class BillPurchaseDAO: DAOFactory
    {
        /// <summary>
        /// 查询进货单
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="purchase"></param>
        /// <returns></returns>
        public BillPurchase GetBillPurchase(DbConnection conn, BillPurchase purchase)
        {
            BillPurchase result = null;
            try
            {
                StringBuilder sb = new StringBuilder("select * from bill_purchase where id>0");
                Hashtable paramtable = new Hashtable();
                this.AddParameters(sb, paramtable, purchase);

                if (paramtable.Keys.Count > 0)
                {
                    DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                    this.AddCommandParamters(cmd, paramtable);
                    DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                    DataTable dt = new DataTable("bill_purchase");
                    da.Fill(dt);

                    if (dt != null && dt.Rows.Count == 1)
                    {
                        DataRow row = dt.Rows[0];
                        result = new BillPurchase();
                        this.DataRowToModel(row, result);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 查询进货单列表
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="purchase"></param>
        /// <returns></returns>
        public DataTable GetBillPurchaseTable(DbConnection conn, BillPurchase purchase)
        {
            DataTable dt = null;
            try
            {
                StringBuilder sb = new StringBuilder("select * from bill_purchase where id>0");
                Hashtable paramtable = new Hashtable();
                this.AddParameters(sb, paramtable, purchase);

                if (!String.IsNullOrEmpty(purchase.StartDate) && !String.IsNullOrEmpty(purchase.EndDate))
                {
                    sb.Append(" and (date(purchase_date)>=@startdate and date(purchase_date)<=@enddate)");
                    paramtable.Add("startdate", purchase.StartDate);
                    paramtable.Add("enddate", purchase.EndDate);
                }

                this.GenerateOrderAndPageSQL(sb, paramtable, purchase);

                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                this.AddCommandParamters(cmd, paramtable);
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                dt = new DataTable("bill_purchase");
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 查询进货单记录数
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="purchase"></param>
        /// <returns></returns>
        public long GetBillPurchaseRecord(DbConnection conn, BillPurchase purchase)
        {
            long record = 0;
            try
            {
                StringBuilder sb = new StringBuilder("select count(id) as c from bill_purchase where id>0");
                Hashtable paramtable = new Hashtable();
                this.AddParameters(sb, paramtable, purchase);

                if (!String.IsNullOrEmpty(purchase.StartDate) && !String.IsNullOrEmpty(purchase.EndDate))
                {
                    sb.Append(" and (date(purchase_date)>=@startdate and date(purchase_date)<=@enddate)");
                    paramtable.Add("startdate", purchase.StartDate);
                    paramtable.Add("enddate", purchase.EndDate);
                }

                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                this.AddCommandParamters(cmd, paramtable);
                Object obj = cmd.ExecuteScalar();
                if (obj != null)
                    record = (long)obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return record;
        }

        /// <summary>
        /// 插入进货单
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="purchase"></param>
        /// <returns></returns>
        public long InsertBillPurchase(DbConnection conn, BillPurchase purchase)
        {
            long id = 0;
            try
            {
                id = (long)this.Insert(conn, purchase);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return id;
        }

        /// <summary>
        /// 更新进货单
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="purchase"></param>
        /// <returns></returns>
        public int UpdateBillPurchase(DbConnection conn, BillPurchase purchase)
        {
            int flag = 0;
            try
            {
                flag = this.Update(conn, purchase);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        /// <summary>
        /// 删除进货单
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="purchase"></param>
        /// <returns></returns>
        public int DeleteBillPurchase(DbConnection conn, BillPurchase purchase)
        {
            int count = 0;
            try
            {
                count = this.Delete(conn, purchase);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return count;
        }

        /// <summary>
        /// 更新欠款金额
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="purchase"></param>
        /// <returns></returns>
        public int UpdateDebtAmount(DbConnection conn, BillPurchase purchase)
        {
            int flag = 0;
            try
            {
                String sql = "update bill_purchase set debt_amount=@debtAmount where purchase_no=@purchaseno";
                DbCommand cmd = CommandFactory.CreateCommand(sql, conn);

                Hashtable paramtable = new Hashtable();
                paramtable.Add("debtAmount", purchase.DebtAmount);
                paramtable.Add("purchaseno", purchase.PurchaseNo);
                this.AddCommandParamters(cmd, paramtable);

                flag = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
    }
}
